package cake.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cake.dao.CakeDao;
import cake.entity.Cake;

public class CakeDaoImpl implements CakeDao {
	private JdbcTemplate jt = new JdbcTemplate();

	private static class CakeDaoRowMapper implements RowMapper<Cake> {
		@Override
		public Cake mapRow(ResultSet rs) throws SQLException {
			return new Cake(rs.getInt("id"), rs.getString("name"), rs.getInt("categoryid"), rs.getFloat("price"),
					rs.getInt("stock"), rs.getBoolean("cakeStatus"), rs.getString("mainimg"));
		}

	}

	private static class CakeDaoWithCountRowMapper implements RowMapper<Cake> {

		@Override
		public Cake mapRow(ResultSet rs) throws SQLException {
			return new Cake(rs.getInt("id"), rs.getString("name"), rs.getInt("categoryid"), rs.getFloat("price"),
					rs.getInt("stock"), rs.getBoolean("cakeStatus"), rs.getString("mainimg"), rs.getInt("count"));
		}

	}

	@Override
	public boolean addCake(Cake cake) {
		String sql = "insert into cake(name,categoryid,price,Stock,cakeStatus,mainimg) values(?,?,?,?,?,?)";
		return jt.update(sql, cake.getName(), cake.getCategoryid(), cake.getPrice(), cake.getStock(),
				cake.isCakeStatus(), cake.getMainimg());
	}

	@Override
	public boolean updateCake(Cake cake, int id) {
		String sql = "update cake set name=?,categoryid=?,price=?,Stock=?,cakeStatus=?,mainimg=? where id=?";
		return jt.update(sql, cake.getName(), cake.getCategoryid(), cake.getPrice(), cake.getStock(),
				cake.isCakeStatus(), cake.getMainimg(), id);
	}

	@Override
	public List<Cake> getAllCake() {
		String sql = "select * from cake";
		return jt.query(sql, new CakeDaoRowMapper());
	}

	@Override
	public Cake getCakeByName(String name) {
		String sql = "select * from cake where name=?";
		return jt.queryOne(sql, new CakeDaoRowMapper(), name);
	}

	@Override
	public Cake getCakeById(int id) {
		String sql = "select * from cake where id=?";
		return jt.queryOne(sql, new CakeDaoRowMapper(), id);
	}

	@Override
	public List<Cake> getCakeByCategory(int cid, int pageNum, int pageSize) {
		ArrayList parameters = new ArrayList();
		String sql = "select * from cake where 1=1";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		parameters.add((pageNum - 1) * pageSize);
		parameters.add(pageSize);
		sql += " limit ?,?";
		return jt.query(sql, new CakeDaoRowMapper(), parameters.toArray());
	}

	public List<Cake> getCakeByCategoryId(int cid) {
		String sql = "select * from cake where categoryId=?";
		return jt.query(sql, new CakeDaoRowMapper(), cid);
	}

	public List<Cake> getCakeByCategory(int cid, String title, int pageNum, int pageSize) {
		ArrayList parameters = new ArrayList();
		String sql = "select * from cake where 1=1";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		if (title != null && !"".equals(title)) {
			sql += " and name like ?";
			parameters.add("%" + title + "%");
		}
		parameters.add((pageNum - 1) * pageSize);
		parameters.add(pageSize);
		sql += " limit ?,?";
		return jt.query(sql, new CakeDaoRowMapper(), parameters.toArray());
	}	
	@Override
	public int totalCakeByCategoryId(int cid, String title) {
		ArrayList parameters = new ArrayList();
		String sql = "select count(id) from cake where 1=1";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		if (title != null && !"".equals(title)) {
			sql += " and name like ?";
			parameters.add("%" + title + "%");
		}
		return jt.queryOne(sql, new RowMapper<Integer>() {

			@Override
			public Integer mapRow(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		}, parameters.toArray());
	}

	@Override
	public int totalCakeByCategoryId(int cid) {
		ArrayList parameters = new ArrayList();
		String sql = "select count(id) from cake where 1=1";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		return jt.queryOne(sql, new RowMapper<Integer>() {

			@Override
			public Integer mapRow(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		}, parameters.toArray());
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return super.toString();
	}

	public Cake getCakeByCidAndUid(int cid, int uid) {
		String sql = "select c.*,s.count count from cake c left join shoppingcar s on s.cid=c.id where s.cid=? and s.uid=?";
		return jt.queryOne(sql, new CakeDaoWithCountRowMapper(), cid, uid);
	}

	public boolean updateStock(int cid, int count) {
		String sql = "update cake set stock=? where id=?";
		Cake cake = this.getCakeById(cid);
		return jt.update(sql, cake.getStock() - count, cid);
	}

	@Override
	public List<Cake> getCakeByCategoryAndStatus(int cid, int pageNum, int pageSize) {
		ArrayList parameters = new ArrayList();
		String sql = "select * from cake where cakestatus=true";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		parameters.add((pageNum - 1) * pageSize);
		parameters.add(pageSize);
		sql += " limit ?,?";
		return jt.query(sql, new CakeDaoRowMapper(), parameters.toArray());
	}

	@Override
	public int totalCakeByCategoryIdAndStatus(int cid) {
		ArrayList parameters = new ArrayList();
		String sql = "select count(id) from cake where cakestatus=true";
		if (cid != 0) {
			sql += " and categoryId=?";
			parameters.add(cid);
		}
		return jt.queryOne(sql, new RowMapper<Integer>() {

			@Override
			public Integer mapRow(ResultSet rs) throws SQLException {
				return rs.getInt(1);
			}
		}, parameters.toArray());
	}

}
